In some cases, a user might want to receive business information about an enterprise. For example, a user might want to create a query to view and/or analyze information from an enterprise data store about the enterprise's revenue or profit in accordance with various regions, time periods, products, etc. Query languages, such as the Structured Query Language (“SQL”), may be particularly suited for retrieval of data from data stores, regardless of the schema of the data. However, SQL may not be particularly suited to data analysis, as it lacks the expressiveness to specify complex, high-level calculations. For example, SQL may provide calculation of only one table at a time and lack constructs such as calculated members, join abstraction, and the abstraction of aggregation functions.
In contrast, Multi-Dimensional eXpressions (“MDX”) is a language providing more meaningful multi-dimensional analytical queries including calculated measures, calculated members, hierarchical navigation support, and heterogeneous member sets. MDX is therefore commonly used to provide advanced analysis.
MDX, however, requires an underlying multi-dimensional model (i.e., an information Cube) which must be authored so as to conform to particular structural requirements. Typically, an information Cube represents a set of independent coordinates in an N-dimensional space, each point of which contains a scalar value (i.e., a string or numeral). MDX allows programmers to specify sets of coordinates in this space and to retrieve the values corresponding to the coordinates.
Authoring a Cube on top of, for instance, a transactional data model of an application can be a relatively costly design task which forces designers to make compromises. For example, a designer may typically determine a default hierarchy to navigate each dimension of a Cube. Also, all dimensions in a Cube must be orthogonal, and, as a result, relations that exist between dimensions in the original schema may be lost after the original schema is projected onto a Cube schema.
Note that data analysis often requires the specification of complex pieces of data, such as an entire report or a dashboard, that can only be materialized in a complex database schema containing, for instance, multiple fact tables sharing some but not all dimension tables. However, SQL, MDX, and other existing query languages for query and analysis are limited in regards to the “shape” of the data sets that a query can return. An SQL SELECT statement, however complex, always computes a single table. Similarly, an MDX statement always brings back a “cube slice” or star schema that includes only one fact table with foreign keys to zero or more independent dimension tables.
Therefore, if a report or dashboard requires more than the above-described simple data topologies, multiple queries to the underlying system must be issued. Then, the client application must reconcile the data returned by the multiple queries, which may involve additional data processing. Consequently, the atomicity of the read transaction is not guaranteed. In addition, the creation and understanding of queries in such approaches can be a difficult and error-prone task that requires specialized training and experience to be successful.
Describing complex data topologies may rapidly become very difficult and lead to large and intricate expressions. It may therefore be desirable to provide systems and methods to facilitate the use of business intelligence language macro expressions in an intuitive and flexible manner. These macro expressions may play the same role as functions in conventional programming languages, making it possible to reuse repeated computations. As a result, the definition and maintenance of complex expressions may be made easier.